-- This scripts contains following function's definition:
-- =============================================================================
-- NULL 相关函数
--   注意： value 效果等同于通用函数 coalesce，在条件允许的情况下，建议修改 SQL 使用 coalesce 函数，该函数几乎在各个数据库中均支持
--   value(text,text)
--   value(numeric,numeric)
--   value(timestamp,timestamp)
--   value(timestamptz,timestamptz)

-- 字符函数
--   posstr(text,text)
--   locate_in_string(text,text,int4,int4,text)
--   regexp_match_count(text,text,int4,text,text)

-- 数字函数

-- 时间函数
--   year(text)
--   year(timestamptz)
--   year(interval)
--   month(text)
--   month(timestamptz)
--   month(interval)
--   quarter(timestamptz)
--   week(timestamptz)
--   day(text)
--   day(timestamptz)
--   day(interval)
--   hour(text)
--   hour(timestamptz)
--   hour(interval)
--   minute(text)
--   minute(timestamptz)
--   minute(interval)
--   second(text)
--   second(timestamptz)
--   second(interval)
--   days(timestamptz)
--   dayofyear(timestamptz)
--   dayofweek(timestamptz)
--   dayofweek_iso(timestamptz)
--   dayname(timestamptz)
--   monthname(timestamptz)
--   midnight_seconds(timestamptz)
--   next_day(timestamptz,text,text)
--   next_month(timestamptz)
--   next_quarter(timestamptz)
--   next_week(timestamptz)
--   next_year(timestamptz)
--   last_day(timestamptz)
--   first_day(timestamptz)
--   this_month(timestamptz)
--   this_quarter(timestamptz)
--   this_week(timestamptz)
--   this_year(timestamptz)
--   days_between(timestamptz,timestamptz)
--   years_between(timestamptz,timestamptz)
--   ymd_between(timestamptz,timestamptz)

-- 其他函数

-- todo
-- =============================================================================


set client_min_messages='warning';
set time zone 'UTC';
set statement_timeout = 60000;


-- =============================================================================
-- Create Schema
-- =============================================================================
do $$
declare
    l_cnt       bigint;
    l_version   varchar(10);
begin
    set client_min_messages='warning';
    select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'compat_tools';
    if l_cnt = 0
    then
        create schema compat_tools;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table
-- =============================================================================
create table if not exists compat_tools.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION, PROCEDURE
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);


-- =============================================================================
-- Table: compat_tools.pg_function_list
-- =============================================================================
drop table if exists compat_tools.pg_function_list;
create table compat_tools.pg_function_list as
select p.oid
     , l.lanname as language
     , n.nspname as schema_name
     , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as function_name
  from (select oid
             , pronamespace
             , proname
             , prolang
             , case when proallargtypes is null then proargtypes else proallargtypes end as proallargtypes
             , generate_series(1, array_length(case when proallargtypes is null then proargtypes else proallargtypes end, 1)) as id
             , unnest(case when proallargtypes is null then proargtypes else proallargtypes end) as protype
          from pg_catalog.pg_proc
       ) as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
  join pg_catalog.pg_type as t on p.protype = t.oid
  left join pg_catalog.pg_type as a on t.typcategory = 'A' and t.typelem = a.oid   -- for array type
 group by p.proname, p.proallargtypes, l.lanname, p.oid, n.nspname
 union all
select p.oid, l.lanname, n.nspname, p.proname||'()'
  from pg_catalog.pg_proc as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
 where (oidvectortypes(p.proargtypes) is null or oidvectortypes(p.proargtypes) = '');


-- =============================================================================
-- Table: compat_tools.pg_depend_list
-- =============================================================================
drop table if exists compat_tools.pg_depend_list;
create table compat_tools.pg_depend_list as
select distinct dep.classid::regclass::text as object_type
     , coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) as schema_name
     , coalesce(typ.typname, coalesce(cls.relname, dep.objid::text)) as object_name
     , dep.refclassid::regclass::text as ref_object_type
     , cpt.object_name as ref_object_name
     , cpt.compat_type
     , case dep.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else dep.deptype::text end as DEPENDENCY_TYPE
  from pg_depend as dep
  join (select v.compat_type, v.object_name, l.language, coalesce(l.oid, c.oid) as oid
          from compat_tools.compat_version as v
          left join compat_tools.pg_function_list as l on v.object_name = l.schema_name||'.'||l.function_name
          left join (select cls.oid, nsp.nspname||'.'||cls.relname object_name
                       from pg_catalog.pg_class as cls
                       join pg_catalog.pg_namespace as nsp on cls.relnamespace = nsp.oid
                    ) as c on v.object_name = c.object_name
         where v.compat_type in ('aggregate', 'procedure', 'function', 'view')) as cpt on dep.refobjid = cpt.oid
  left join pg_type as typ on dep.classid = 'pg_type'::regclass and dep.objid = typ.oid
  left join pg_namespace as typ_n on typ_n.oid = typ.typnamespace
  left join pg_rewrite as rwt on dep.classid = 'pg_rewrite'::regclass and dep.objid = rwt.oid
  left join pg_class as cls on rwt.ev_class = cls.oid
  left join pg_namespace as cls_n on cls_n.oid = cls.relnamespace
 where coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) != 'compat_tools';


-- =============================================================================
-- Version Function
-- =============================================================================
drop function if exists compat_tools.drop_compat_function(varchar, varchar, varchar, varchar, varchar);
create or replace function compat_tools.drop_compat_function( p_object_type    varchar(10)
                                                            , p_object_name    varchar(128)
                                                            , p_object_version varchar(10)
                                                            , p_function_lang  varchar(16)  default 'sql'
                                                            , p_object_schema  varchar(128) default 'pg_catalog')
returns boolean as $$
declare
    l_version    varchar(10);
    l_language   varchar(16);
    l_result     boolean       := 'true';
    l_operation  varchar(128);
    l_depend_cnt bigint;
    l_app_name   varchar(128)  := current_setting('application_name');
begin
    CREATE temporary table if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    );

    -- 字符串参数统一转小写
    p_object_name := lower(p_object_name);
    p_object_type := lower(p_object_type);
    p_object_schema := lower(p_object_schema);

    select max(object_version) into l_version
      from compat_tools.compat_version
     where object_name = p_object_schema||'.'||p_object_name
       and compat_type = p_object_type;

    -- 获取已有同名同参数函数/存储过程的语言，存入 l_language 变量
    select max(language) into l_language
      from compat_tools.pg_function_list
     where schema_name = p_object_schema
       and function_name = p_object_name;

    -- 获取非 Compat Tools 依赖对象数量
    select count(*) into l_depend_cnt
      from compat_tools.pg_depend_list
     where ref_object_name = p_object_schema||'.'||p_object_name;

    if l_language is null
    then
        l_operation := 'Initial creation';
    elsif l_language != p_function_lang
    then
        l_result = 'false';
        l_operation := 'Skip due to language';
    elsif l_version is null
    then
        l_operation := 'Initial creation (Ver)';
    elsif l_version < p_object_version
    then
        l_operation := 'Upgrade';
    else
        l_result = 'false';
        l_operation := 'Skip due to version';
    end if;

    if l_app_name != 'checkMe'
    then
        if l_result
        then
            begin
                -- 若系统中存在非 compat_tools 对象依赖本对象，无法删除，可尝试直接创建
                if l_depend_cnt = 0
                then
                    if instr(p_object_name, '(') > 0
                    then
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||replace(p_object_name, '(', '"(')||' cascade';
                    else
                        execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||p_object_name||'" cascade';
                    end if;

                    insert into compat_tools.compat_version
                    values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
                    ON DUPLICATE KEY UPDATE object_version = p_object_version;
                else
                    l_operation := l_operation||' - dependence';
                end if;
            exception
                when others then
                    l_result := 'false';
                    get stacked diagnostics l_operation = message_text;
                    l_operation = substr(l_operation, 1, 32);
            end;
        else
            insert into compat_tools.compat_version
            values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
            ON DUPLICATE KEY UPDATE NOTHING;
        end if;
    end if;

    -- 插入本次临时结果表
    insert into temp_result values ( p_object_type
                                   , p_object_schema||'.'||p_object_name
                                   , l_version
                                   , p_object_version
                                   , l_language
                                   , p_function_lang
                                   , l_operation);

    -- 返回函数结果
    if l_app_name = 'checkMe'
    then
        return 'false';
    else
        return l_result;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Testing Table/Procedure
-- =============================================================================
create table if not exists compat_tools.compat_testing
(
    test_expr       text,      -- 测试表达式
    test_result     text,      -- 表达式结果
    expect_result   text,      -- 预期结果
    test_ok         bool,      -- 测试是否通过
    test_timestamp  timestamp  default now(), -- 测试时间
    constraint pk_compat_testing_expr primary key(test_expr)
);

drop procedure if exists compat_tools.f_unit_test(text, text, text, text);
create or replace procedure compat_tools.f_unit_test( p_test_expr     text
                                                    , p_expect        text
                                                    , p_compare_type  text  default '='
                                                    , p_db_compat     text  default NULL)
as
declare
    l_compat_valid bool    := 'true'::bool;
    l_error_code   text;
    l_error_mesg   text;
    l_test_result  text;
    l_test_ok      bool;
    l_app_name     varchar(128)  := current_setting('application_name');
begin
    if l_app_name != 'checkMe'
    then
        if p_db_compat is not null
        then
            select count(*)::bool
              into l_compat_valid
              from pg_database
             where datname = current_database()
               and datcompatibility = p_db_compat;
        end if;

        if l_compat_valid
        then
            execute immediate 'select '||p_test_expr||', cast('||p_test_expr||' as text) '||p_compare_type||' '||p_expect
               into l_test_result, l_test_ok;
            insert into compat_tools.compat_testing
            values (p_test_expr, l_test_result, p_compare_type||' '||p_expect, l_test_ok)
            on DUPLICATE KEY UPDATE test_result = EXCLUDED.test_result
                                  , expect_result = EXCLUDED.expect_result
                                  , test_ok = EXCLUDED.test_ok
                                  , test_timestamp = now();
        end if;
    end if;
exception
    when others then
        get stacked diagnostics l_error_code = returned_sqlstate, l_error_mesg = message_text;
        insert into compat_tools.compat_testing
        values (p_test_expr, l_error_code||': '||l_error_mesg, p_compare_type||' '||p_expect, l_error_code||': '||l_error_mesg = trim(p_expect, ''''))
        on DUPLICATE KEY UPDATE test_result = EXCLUDED.test_result
                              , expect_result = EXCLUDED.expect_result
                              , test_ok = EXCLUDED.test_ok
                              , test_timestamp = now();
end;
/



-- =============================================================================
-- Version Comparison Function
-- Result:
--     version_a > version_b  => 1
--     version_a = version_b  => 0
--     version_a < version_b  => -1
-- =============================================================================
create or replace function pg_catalog.f_version_compare ( version_a text
                                                        , version_b text)
returns int IMMUTABLE strict as $$
declare
    l_rec   record;
begin
    if version_a !~ '^\d+(\.\d+)*$' or version_b !~ '^\d+(\.\d+)*$'
    then
        return null;
    end if;
    for l_rec in select coalesce(t1.ver, -1) as ver_1, coalesce(t2.ver, -1) as ver_2
                   from (select rownum as id, t1::int as ver from regexp_split_to_table(version_a, '\.') as t1) as t1
                   full outer join (select rownum as id, t2::int as ver from regexp_split_to_table(version_b, '\.') as t2) as t2
                     on t1.id = t2.id
    loop
        if l_rec.ver_1 > l_rec.ver_2
        then
            return 1;
        elsif l_rec.ver_1 < l_rec.ver_2
        then
            return -1;
        end if;
    end loop;

    return 0;
end;
$$ language plpgsql;
-- =========================================================================
-- 测试用例：
-- ----------
-- select compat_tools.f_unit_test('f_version_compare (''1.1.1'', ''2.0.0'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''2.1.1'', ''2.0.0'')', '1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3'')', '0');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.12.3'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3.4'', ''1.2.3'')', '1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3.4'')', '-1');
-- select compat_tools.f_unit_test('f_version_compare (''1.2...3'', ''1.2.3.4'')', 'NULL', 'IS');
-- select compat_tools.f_unit_test('f_version_compare (null, ''1.2.3.4'')', 'NULL', 'IS');
-- select * from compat_tools.compat_testing where test_expr like 'f_version_compare%';
-- =========================================================================



-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $VIEW_CREATION$
declare
    l_cnt    bigint;
    l_result text;
begin
    -- =========================================================================
    -- 注意： value 效果等同于 coalesce 函数，在条件允许的情况下，建议修改 SQL 使用 coalesce 函数，几乎在各个数据库中均支持
    -- value(text,text)
    -- value(numeric,numeric)
    -- value(timestamp,timestamp)
    -- value(timestamptz,timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'value(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.value(p1 text, p2 text)
        RETURNS text IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'value(numeric,numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.value(p1 numeric, p2 numeric)
        RETURNS numeric IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'value(timestamp,timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.value(p1 timestamp, p2 timestamp)
        RETURNS timestamp IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'value(timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.value(p1 timestamptz, p2 timestamptz)
        RETURNS timestamptz IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(value(123, 1111))', '''numeric''') into l_result;
    select compat_tools.f_unit_test('value(123, 1111)', '123') into l_result;
    select compat_tools.f_unit_test('value(null, 1111)', '1111') into l_result;
    select compat_tools.f_unit_test('value(123, null)', '123') into l_result;

    select compat_tools.f_unit_test('pg_typeof(value(''xyz'', ''abc''))', '''text''') into l_result;
    select compat_tools.f_unit_test('value(''xyz'', ''abc'')', '''xyz''') into l_result;
    select compat_tools.f_unit_test('value(null, ''abc'')', '''abc''') into l_result;
    select compat_tools.f_unit_test('value(''xyz'', null)', '''xyz''') into l_result;

    select compat_tools.f_unit_test('pg_typeof(value(''2012-12-12''::timestamp, ''2021-03-04''::timestamp))', '''timestamp without time zone''') into l_result;
    select compat_tools.f_unit_test('value(''2012-12-12''::timestamp, ''2021-03-04''::timestamp)', '''2012-12-12 00:00:00''') into l_result;
    select compat_tools.f_unit_test('value(null, ''2021-03-04''::timestamp)', '''2021-03-04 00:00:00''') into l_result;
    select compat_tools.f_unit_test('value(''2012-12-12''::timestamp, null)', '''2012-12-12 00:00:00''') into l_result;

    select compat_tools.f_unit_test('value(null, null)', 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- posstr(text,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'posstr(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.posstr(p_source text, p_str text)
        RETURNS int IMMUTABLE AS $$
        select instr(p_source, p_str);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('posstr(''Hongye'', ''ong'')', '2') into l_result;
    select compat_tools.f_unit_test('posstr(''Hongye'', ''H'')', '1') into l_result;
    select compat_tools.f_unit_test('posstr(''Hongye'', ''Hxxx'')', '0') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- year(text)
    -- year(timestamptz)
    -- year(interval)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'year(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.year(p_text text)
        RETURNS int IMMUTABLE strict AS $$
        select extract('year' from p_text::timestamp)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'year(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.year(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('year' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'year(interval)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.year(p_interval interval)
        RETURNS int IMMUTABLE strict AS $$
        select extract('year' from p_interval)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('year(''2012-12-12 12:12:12''::timestamp)', '2012') into l_result;
    select compat_tools.f_unit_test('year(''2021-11-13 14:15:16''::timestamptz)', '2021') into l_result;
    select compat_tools.f_unit_test('year(interval ''1 year 2 month'')', '1') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- month(text)
    -- month(timestamptz)
    -- month(interval)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'month(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.month(p_text text)
        RETURNS int IMMUTABLE strict AS $$
        select extract('month' from p_text::timestamp)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'month(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.month(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('month' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'month(interval)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.month(p_interval interval)
        RETURNS int IMMUTABLE strict AS $$
        select extract('month' from p_interval)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('month(''2012-12-12 12:12:12''::timestamp)', '12') into l_result;
    select compat_tools.f_unit_test('month(''2021-11-13 14:15:16''::timestamptz)', '11') into l_result;
    select compat_tools.f_unit_test('month(interval ''1 year 2 month'')', '2') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- quarter(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'quarter(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.quarter(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('quarter' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('quarter(''2012-12-12 12:12:12''::timestamp)', '4') into l_result;
    select compat_tools.f_unit_test('quarter(''2021-01-13 14:15:16''::timestamptz)', '1') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- week(timestamptz)
    -- =========================================================================
    -- 计算思路：
    --     每年的第一周时间大概率不足7天，由于 openGauss 不存在从周日计数的年内周数，
    --     所以按照年内天数除以 7 计算周数，并补齐第一周的天数，
    --     依据当年一月一号是周几： 周一补一天 ... 周六补6天，周日不补
    --     select extract('isodow' from trunc('1921-01-01'::timestamp))  -- 6
    --     select extract('isodow' from trunc('2021-01-01'::timestamp))  -- 5
    --     select extract('isodow' from trunc('1021-01-01'::timestamp))  -- 1
    --     1    6  +1
    --     2    5  +2
    --     3    4  +3
    --     4    3  +4
    --     5    2  +5
    --     6    1  +6
    --     7    0  +7
    --     select ceil((extract('doy' from '2021-01-01'::timestamp) + 5)/7)
    --          , ceil((extract('doy' from '2021-01-02'::timestamp) + 5)/7)
    --          , ceil((extract('doy' from '2021-01-03'::timestamp) + 5)/7)
    --          , ceil((extract('doy' from '2021-01-09'::timestamp) + 5)/7)
    --          , ceil((extract('doy' from '2021-01-10'::timestamp) + 5)/7) from dual;
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'week(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.week(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select case extract('isodow' from date_trunc('year', p_ts))
               when 7 then ceil(extract('doy' from p_ts)/7)::int
               else ceil((extract('doy' from p_ts) + extract('isodow' from date_trunc('year', p_ts)))/7)::int
               end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('week(''2021-01-01'')', '1') into l_result;
    select compat_tools.f_unit_test('week(''2021-01-02'')', '1') into l_result;
    select compat_tools.f_unit_test('week(''2021-01-03'')', '2') into l_result;
    select compat_tools.f_unit_test('week(''2021-01-09'')', '2') into l_result;
    select compat_tools.f_unit_test('week(''2021-01-10'')', '3') into l_result;

    select compat_tools.f_unit_test('week(''1921-01-01'')', '1') into l_result;
    select compat_tools.f_unit_test('week(''1921-01-02'')', '2') into l_result;
    select compat_tools.f_unit_test('week(''1921-01-03'')', '2') into l_result;
    select compat_tools.f_unit_test('week(''1921-01-09'')', '3') into l_result;
    select compat_tools.f_unit_test('week(''1921-01-10'')', '3') into l_result;

    select compat_tools.f_unit_test('week(''2121-01-01'')', '1') into l_result;
    select compat_tools.f_unit_test('week(''2121-01-02'')', '1') into l_result;
    select compat_tools.f_unit_test('week(''2121-01-03'')', '1') into l_result;
    select compat_tools.f_unit_test('week(''2121-01-09'')', '2') into l_result;
    select compat_tools.f_unit_test('week(''2121-01-10'')', '2') into l_result;

    select compat_tools.f_unit_test('week(''1004-01-01'')', '1') into l_result;
    select compat_tools.f_unit_test('week(''1004-01-07'')', '1') into l_result;
    select compat_tools.f_unit_test('week(''1004-01-08'')', '2') into l_result;
    select compat_tools.f_unit_test('week(''1004-01-09'')', '2') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- day(text)
    -- day(timestamptz)
    -- day(interval)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'day(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.day(p_text text)
        RETURNS int IMMUTABLE strict AS $$
        select extract('day' from p_text::timestamp)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'day(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.day(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('day' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'day(interval)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.day(p_interval interval)
        RETURNS int IMMUTABLE strict AS $$
        select extract('day' from p_interval)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('day(''2021-01-01'')', '1') into l_result;
    select compat_tools.f_unit_test('day(''2021-01-02'')', '2') into l_result;
    select compat_tools.f_unit_test('day(''2021-01-03'')', '3') into l_result;
    select compat_tools.f_unit_test('day(''2021-01-09'')', '9') into l_result;
    select compat_tools.f_unit_test('day(''2021-01-10'')', '10') into l_result;

    select compat_tools.f_unit_test('day(interval ''1 year 2 month'')', '0') into l_result;
    select compat_tools.f_unit_test('day(interval ''1 day 2 minutes'')', '1') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- hour(text)
    -- hour(timestamptz)
    -- hour(interval)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'hour(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.hour(p_text text)
        RETURNS int IMMUTABLE strict AS $$
        select extract('hour' from p_text::timestamp)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'hour(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.hour(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('hour' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'hour(interval)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.hour(p_interval interval)
        RETURNS int IMMUTABLE strict AS $$
        select extract('hour' from p_interval)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('hour(''2021-01-01 12:12:12'')', '12') into l_result;
    select compat_tools.f_unit_test('hour(''2021-01-01 00:12:12'')', '0') into l_result;
    select compat_tools.f_unit_test('hour(interval ''1 year 2 month'')', '0') into l_result;
    select compat_tools.f_unit_test('hour(interval ''1 day 2 hour'')', '2') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- minute(text)
    -- minute(timestamptz)
    -- minute(interval)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'minute(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.minute(p_text text)
        RETURNS int IMMUTABLE strict AS $$
        select extract('minute' from p_text::timestamp)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'minute(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.minute(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('minute' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'minute(interval)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.minute(p_interval interval)
        RETURNS int IMMUTABLE strict AS $$
        select extract('minute' from p_interval)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('minute(''2021-01-01 12:12:12'')', '12') into l_result;
    select compat_tools.f_unit_test('minute(''2021-01-01 00:00:12'')', '0') into l_result;
    select compat_tools.f_unit_test('minute(interval ''1 year 2 month'')', '0') into l_result;
    select compat_tools.f_unit_test('minute(interval ''1 day 2 minute'')', '2') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- second(text)
    -- second(timestamptz)
    -- second(interval)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'second(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.second(p_text text)
        RETURNS int IMMUTABLE strict AS $$
        select extract('second' from p_text::timestamp)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'second(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.second(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('second' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'second(interval)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.second(p_interval interval)
        RETURNS int IMMUTABLE strict AS $$
        select extract('second' from p_interval)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('second(''2021-01-01 12:12:12'')', '12') into l_result;
    select compat_tools.f_unit_test('second(''2021-01-01 00:00:00'')', '0') into l_result;
    select compat_tools.f_unit_test('second(interval ''1 year 2 month'')', '0') into l_result;
    select compat_tools.f_unit_test('second(interval ''1 day 2 second'')', '2') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- days(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'days(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.days(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('days' from p_ts - '0001-01-01'::timestamp)::int + 1;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('days(''0001-01-01'')', '1') into l_result;
    select compat_tools.f_unit_test('days(''2021-01-01'')', '737791') into l_result;
    select compat_tools.f_unit_test('days(''1021-12-30'')', '372911') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- dayofyear(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'dayofyear(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.dayofyear(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('doy' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('dayofyear(''0001-01-01'')', '1') into l_result;
    select compat_tools.f_unit_test('dayofyear(''2021-01-01'')', '1') into l_result;
    select compat_tools.f_unit_test('dayofyear(''1021-12-30'')', '364') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- dayofweek(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'dayofweek(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.dayofweek(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('dow' from p_ts)::int + 1;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('dayofweek(''2021-01-01'')', '6') into l_result;
    select compat_tools.f_unit_test('dayofweek(''2021-01-02'')', '7') into l_result;
    select compat_tools.f_unit_test('dayofweek(''2021-01-03'')', '1') into l_result;
    select compat_tools.f_unit_test('dayofweek(''2021-01-09'')', '7') into l_result;
    select compat_tools.f_unit_test('dayofweek(''2021-01-10'')', '1') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- dayofweek_iso(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'dayofweek_iso(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.dayofweek_iso(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('isodow' from p_ts)::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('dayofweek_iso(''2021-01-01'')', '5') into l_result;
    select compat_tools.f_unit_test('dayofweek_iso(''2021-01-02'')', '6') into l_result;
    select compat_tools.f_unit_test('dayofweek_iso(''2021-01-03'')', '7') into l_result;
    select compat_tools.f_unit_test('dayofweek_iso(''2021-01-09'')', '6') into l_result;
    select compat_tools.f_unit_test('dayofweek_iso(''2021-01-10'')', '7') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- dayname(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'dayname(timestamptz)', '2.0')
    then
        CREATE or replace FUNCTION pg_catalog.dayname(p_ts timestamptz)
        RETURNS text IMMUTABLE strict AS $$
        select rtrim(to_char(p_ts, 'Day'));
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('dayname(''2021-01-01'')', '''Friday''') into l_result;
    select compat_tools.f_unit_test('dayname(''2021-01-02'')', '''Saturday''') into l_result;
    select compat_tools.f_unit_test('dayname(''2021-01-03'')', '''Sunday''') into l_result;
    select compat_tools.f_unit_test('dayname(''2021-01-09'')', '''Saturday''') into l_result;
    select compat_tools.f_unit_test('dayname(''2021-01-10'')', '''Sunday''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- monthname(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'monthname(timestamptz)', '2.0')
    then
        CREATE or replace FUNCTION pg_catalog.monthname(p_ts timestamptz)
        RETURNS text IMMUTABLE strict AS $$
        select rtrim(to_char(p_ts, 'Month'));
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('monthname(''2021-01-01'')', '''January''') into l_result;
    select compat_tools.f_unit_test('monthname(''2021-12-02'')', '''December''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- midnight_seconds(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'midnight_seconds(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.midnight_seconds(p_ts timestamptz)
        RETURNS int IMMUTABLE strict AS $$
        select extract('epoch' from p_ts)::int - extract('epoch' from trunc(p_ts))::int;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('midnight_seconds(''2021-01-01 12:12:12'')', '43932') into l_result;
    select compat_tools.f_unit_test('midnight_seconds(''2021-12-02 00:00:00'')', '0') into l_result;
    select compat_tools.f_unit_test('midnight_seconds(''2021-12-02 23:59:59'')', '86399') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- next_day(timestamptz,text,text)
    -- 实现思路：
    --     当日星期  目标星期  加天数
    --     1         1       +7
    --     1         2       +1
    --     1         3       +2
    --     1         4       +3
    --     1         5       +4
    --     1         6       +5
    --     1         7       +6

    --     2         1       +6
    --     2         2       +7
    --     2         3       +1
    --     2         4       +2
    --     2         5       +3
    --     2         6       +4
    --     2         7       +5

    --     3         1       +5
    --     3         2       +6
    --     3         3       +7
    --     3         4       +1
    --     3         5       +2
    --     3         6       +3
    --     3         7       +4

    --     加天数计算公式： 目标星期 - 当日星期 (逢 0 变 7 )
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'next_day(timestamptz,text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.next_day( p_ts        timestamptz
                                                      , p_weekday   text
                                                      , p_locale    text)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select p_ts + interval '1 day' * (case when week_target = week_day then 7 else week_target - week_day end)
          from (select case lower(p_weekday)
                       when 'monday' then 1 when 'mon' then 1
                       when 'tuesday' then 1 when 'tue' then 1
                       when 'wednesday' then 1 when 'wed' then 1
                       when 'thursday' then 1 when 'thu' then 1
                       when 'friday' then 1 when 'fri' then 1
                       when 'saturday' then 1 when 'sat' then 1
                       else 7 end as week_target
                     , extract('isodow' from p_ts) as week_day) as t;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('next_day(''2021-01-01 12:12:12'', ''monday'')', '''2021-01-04 12:12:12''') into l_result;
    select compat_tools.f_unit_test('next_day(''2021-03-02'', ''tue'')', '''2021-03-09 00:00:00''') into l_result;
    select compat_tools.f_unit_test('next_day(''2021-12-02 23:59:59'', ''sun'')', '''2021-12-05 23:59:59''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- next_month(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'next_month(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.next_month(p_ts timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select date_trunc('month', p_ts) + interval '1 month';
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(next_month(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('next_month(''2021-01-01 12:12:12'')::timestamp', '''2021-02-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('next_month(''2021-02-28'')::timestamp', '''2021-03-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('next_month(''2021-12-02 23:59:59'')::timestamp', '''2022-01-01 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- next_quarter(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'next_quarter(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.next_quarter(p_ts timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select date_trunc('quarter', p_ts) + interval '3 month';
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(next_quarter(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('next_quarter(''2021-01-01 12:12:12'')::timestamp', '''2021-04-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('next_quarter(''2021-02-28'')::timestamp', '''2021-04-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('next_quarter(''2021-12-02 23:59:59'')::timestamp', '''2022-01-01 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- next_week(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'next_week(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.next_week(p_ts timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select date_trunc('day', p_ts) + interval '1 day' * (7 - extract('dow' from p_ts));
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(next_week(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('next_week(''2021-01-03 12:12:12'')::timestamp', '''2021-01-10 00:00:00''') into l_result;
    select compat_tools.f_unit_test('next_week(''2021-02-28'')::timestamp', '''2021-03-07 00:00:00''') into l_result;
    select compat_tools.f_unit_test('next_week(''2021-12-02 23:59:59'')::timestamp', '''2021-12-05 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- next_year(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'next_year(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.next_year(p_ts timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select date_trunc('year', p_ts) + interval '1 year';
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(next_year(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('next_year(''0001-02-28'')::timestamp', '''0002-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('next_year(''2021-12-02 23:59:59'')::timestamp', '''2022-01-01 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- locate_in_string(text,text,int4,int4,text)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'locate_in_string(text,text,int4,int4,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.locate_in_string( p_source    text
                                                              , p_search    text
                                                              , p_start     int4 default 1
                                                              , p_instance  int4 default 1
                                                              , p_charset   text default 'x')
        RETURNS int IMMUTABLE strict AS $$
        select instr(p_source, p_search, p_start, p_instance);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('locate_in_string(''hongye'', ''h'')', '1') into l_result;
    select compat_tools.f_unit_test('locate_in_string(''hongye'', ''ye'')', '5') into l_result;
    select compat_tools.f_unit_test('locate_in_string(''hongye'', ''xx'')', '0') into l_result;
    select compat_tools.f_unit_test('locate_in_string(null, ''xx'')', 'NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('locate_in_string(''hongye'', null)', 'NULL', 'IS') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- last_day(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'last_day(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.last_day(p_tstz timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select p_tstz + interval '1 day' * (extract(day from date_trunc('month', p_tstz) + interval '1 month -1 day') - extract(day from p_tstz));
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(last_day(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('last_day(''2012-12-12 12:12:12.121212+01:23''::timestamptz)::timestamp', '''2012-12-31 10:49:12.121212''') into l_result;
    select compat_tools.f_unit_test('last_day(''2021-01-01''::timestamp)::timestamp', '''2021-01-31 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- first_day(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'first_day(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.first_day(p_tstz timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select p_tstz - interval '1 day' * (extract(day from p_tstz) - 1);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(first_day(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('first_day(''2012-12-12 12:12:12.121212+01:23''::timestamptz)::timestamp', '''2012-12-01 10:49:12.121212''') into l_result;
    select compat_tools.f_unit_test('first_day(''2021-01-01''::timestamp)::timestamp', '''2021-01-01 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- regexp_match_count(text,text,int4,text,text)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'regexp_match_count(text,text,int4,text,text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_match_count ( p_source     text
                                                                 , p_pattern    text
                                                                 , p_start      int  default 1
                                                                 , p_flag       text default 'c'
                                                                 , p_charset    text default 'x')
        RETURNS int IMMUTABLE strict AS $$
        select length(pg_catalog.regexp_replace(source_str, p_pattern, '#\&', case lower(p_flag) when 'i' then 'gi' else 'g' end))
             - length(source_str)
          from (select substr(p_source, p_start) as source_str) as x;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('regexp_match_count(''abcdA123'', ''a.'')', '1') into l_result;
    select compat_tools.f_unit_test('regexp_match_count(''abcdA123'', ''a.'', 1, ''i'')', '2') into l_result;
    select compat_tools.f_unit_test('regexp_match_count(''abcdA123'', ''a.'', 3)', '0') into l_result;
    select compat_tools.f_unit_test('regexp_match_count(''abcdA123'', ''a.'', 3, ''i'')', '1') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- this_month(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'this_month(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.this_month(p_ts timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select date_trunc('month', p_ts);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(this_month(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('this_month(''2021-01-01 12:12:12'')::timestamp', '''2021-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('this_month(''2021-02-28'')::timestamp', '''2021-02-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('this_month(''2021-12-02 23:59:59'')::timestamp', '''2021-12-01 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- this_quarter(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'this_quarter(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.this_quarter(p_ts timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select date_trunc('quarter', p_ts);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(this_quarter(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('this_quarter(''2021-01-01 12:12:12'')::timestamp', '''2021-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('this_quarter(''2021-02-28'')::timestamp', '''2021-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('this_quarter(''2021-12-02 23:59:59'')::timestamp', '''2021-10-01 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- this_week(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'this_week(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.this_week(p_ts timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select date_trunc('day', p_ts) - interval '1 day' * extract('dow' from p_ts);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(this_week(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('this_week(''2021-01-05 12:12:12'')::timestamp', '''2021-01-03 00:00:00''') into l_result;
    select compat_tools.f_unit_test('this_week(''2021-02-28'')::timestamp', '''2021-02-28 00:00:00''') into l_result;
    select compat_tools.f_unit_test('this_week(''2021-12-02 23:59:59'')::timestamp', '''2021-11-28 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- this_year(timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'this_year(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.this_year(p_ts timestamptz)
        RETURNS timestamptz IMMUTABLE strict AS $$
        select date_trunc('year', p_ts);
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('pg_typeof(this_year(''2021-02-28''))', '''timestamp with time zone''') into l_result;
    select compat_tools.f_unit_test('this_year(''0001-02-28'')::timestamp', '''0001-01-01 00:00:00''') into l_result;
    select compat_tools.f_unit_test('this_year(''2021-12-02 23:59:59'')::timestamp', '''2021-01-01 00:00:00''') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- days_between(timestamptz,timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'days_between(timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.days_between( p_ts1 timestamptz
                                                          , p_ts2 timestamptz)
        RETURNS int8 IMMUTABLE strict AS $$
        select extract('day' from p_ts1 - p_ts2)::bigint;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('days_between(''2004-03-31 12:12:12'', ''2004-02-22'')', '38') into l_result;
    select compat_tools.f_unit_test('days_between(''1014-12-01 12:12:12'', ''2004-03-31'')', '-361345') into l_result;
    select compat_tools.f_unit_test('days_between(''2014-12-31 12:12:12'', ''2004-03-31'')', '3927') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- years_between(timestamptz,timestamptz)
    -- 测试用例：
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'years_between(timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.years_between( p_ts1 timestamptz
                                                           , p_ts2 timestamptz)
        RETURNS int8 IMMUTABLE strict AS $$
        select case when p_ts1 < p_ts2 and to_char(p_ts1, 'MMDDHH24MISSFF') > to_char(p_ts2, 'MMDDHH24MISSFF')
                    then extract('year' from p_ts1)::bigint - extract('year' from p_ts2)::bigint + 1
                    else extract('year' from p_ts1)::bigint - extract('year' from p_ts2)::bigint
               end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('years_between(''2004-01-01 12:12:12'', ''2004-12-31'')', '0') into l_result;
    select compat_tools.f_unit_test('years_between(''1004-01-01 12:12:12'', ''2004-12-31'')', '-1000') into l_result;
    select compat_tools.f_unit_test('years_between(''2014-12-31 12:12:12'', ''2004-01-01'')', '10') into l_result;
    select compat_tools.f_unit_test('years_between(''1004-12-31 12:12:12'', ''2004-01-01'')', '-999') into l_result;
    select compat_tools.f_unit_test('years_between(''1004-12-31 12:12:12'', ''2004-12-31 12:12:11'')', '-999') into l_result;
    select compat_tools.f_unit_test('years_between(''1004-12-31 12:12:12'', ''2004-12-31 12:12:12'')', '-1000') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- ymd_between(timestamptz,timestamptz)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'ymd_between(timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ymd_between( p_ts1 timestamptz
                                                         , p_ts2 timestamptz)
        RETURNS numeric IMMUTABLE strict AS $$
        select case when p_ts1 < p_ts2 and to_char(p_ts1, 'MMDDHH24MISSFF') > to_char(p_ts2, 'MMDDHH24MISSFF')
                    then trunc((to_char(date_trunc('day', p_ts1) + interval '1 day', 'YYYYMMDDHH24MISS.FF')::numeric - to_char(p_ts2, 'YYYYMMDDHH24MISS.FF')::numeric)/1000000)
                    else trunc((to_char(p_ts1, 'YYYYMMDDHH24MISS.FF')::numeric - to_char(p_ts2, 'YYYYMMDDHH24MISS.FF')::numeric)/1000000)
               end;
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('ymd_between(''2004-01-01 12:12:12'', ''2004-12-31'')', '-1129') into l_result;
    select compat_tools.f_unit_test('ymd_between(''1004-01-01 12:12:12'', ''2004-12-31'')', '-10001129') into l_result;
    select compat_tools.f_unit_test('ymd_between(''2014-12-31 12:12:12'', ''2004-01-01'')', '101130') into l_result;
    select compat_tools.f_unit_test('ymd_between(''1004-12-31 12:12:12'', ''2004-01-01'')', '-9990000') into l_result;
    select compat_tools.f_unit_test('ymd_between(''1004-12-31 12:12:12'', ''2004-12-31 12:12:11'')', '-9991130') into l_result;
    select compat_tools.f_unit_test('ymd_between(''1004-12-31 12:12:12'', ''2004-12-31 12:12:12'')', '-10000000') into l_result;
    -- =========================================================================


    -- =========================================================================
    -- digits(int2)
    -- digits(int4)
    -- digits(int8)
    -- digits(text)
    -- digits(numeric)
    -- =========================================================================
    if compat_tools.drop_compat_function('function', 'digits(int2)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.digits(int2)
        RETURNS text IMMUTABLE strict AS $$
        select lpad(abs($1)::text, 5, '0');
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'digits(int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.digits(int4)
        RETURNS text IMMUTABLE strict AS $$
        select lpad(abs($1)::text, 10, '0');
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'digits(int8)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.digits(int8)
        RETURNS text IMMUTABLE strict AS $$
        select lpad(abs($1)::text, 19, '0');
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'digits(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.digits(text)
        RETURNS text IMMUTABLE strict AS $$
        select lpad(floor(abs($1::numeric) * 1000000)::text, 31, '0');
        $$ LANGUAGE sql;
    end if;
    if compat_tools.drop_compat_function('function', 'digits(numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.digits(numeric)
        RETURNS text IMMUTABLE strict AS $$
        select replace(abs($1)::text, '.', '');
        $$ LANGUAGE sql;
    end if;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    -- select digits(cast(123.12345678 as decimal(20,10))) from dual;  -- 00000001231234567800
    select compat_tools.f_unit_test('digits(''123'')', '''0000000000000000000000123000000''') into l_result;
    select compat_tools.f_unit_test('digits(''123.1234567'')', '''0000000000000000000000123123456''') into l_result;
    select compat_tools.f_unit_test('digits(''-123.1234567'')', '''0000000000000000000000123123456''') into l_result;
    select compat_tools.f_unit_test('digits(123.12345678)', '''12312345678''') into l_result;
    select compat_tools.f_unit_test('digits(-123.12345678)', '''12312345678''') into l_result;
    select compat_tools.f_unit_test('digits(cast(-123 as smallint))', '''00123''') into l_result;
    select compat_tools.f_unit_test('digits(cast(123 as smallint))', '''00123''') into l_result;
    select compat_tools.f_unit_test('digits(cast(123 as int))', '''0000000123''') into l_result;
    select compat_tools.f_unit_test('digits(cast(123 as bigint))', '''0000000000000000123''') into l_result;
    -- =========================================================================

end;
$VIEW_CREATION$ language plpgsql;


-- Show result & Exit
do $RESULT_SUMMARY$
declare
    l_app_name      text  := current_setting('application_name');
    l_failed_cnt    bigint;
begin
    set client_min_messages='notice';
    if l_app_name not in ('runMe', 'checkMe')
    then
        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Compat Object List: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   |' || pad_char
                              || rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
                            from (select greatest(max(length(object_type)), 5) max_object_type
                                       , greatest(max(length(object_name)), 6) max_object_name
                                       , greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
                                       , greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
                                       , greatest(max(length(operation)), 9) max_operation
                                    from temp_result) l
                            join (select 'type' as object_type
                                       , 'name' as object_name
                                       , 'version' as object_version
                                       , 'language' as object_language
                                       , 'operation' as operation
                                       , ' ' as pad_char
                                   union all
                                  select '-' as object_type
                                       , '-' as object_name
                                       , '-' as object_version
                                       , '-' as object_language
                                       , '-' as operation
                                       , '-' as pad_char
                                   union all
                                  select object_type, object_name
                                       , case when local_version = script_version then local_version else local_version || ' => ' || script_version end as object_version
                                       , case when local_language = script_language then local_language else local_language || ' => ' || script_language end as object_language
                                       , operation, ' ' from temp_result) r on 1 = 1
        loop
            raise notice '%', l_app_name;
        end loop;

        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Test Summary: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   | result_type | case_count | start_time          | complete_time       |'
                           union all
                          select '   |-------------|------------|---------------------|---------------------|'
                           union all
                          select '   | '
                              || rpad((case when test_ok then 'PASSED' when not test_ok then 'FAILED' else 'NULL' end)::text, 11)
                              || ' | '
                              || lpad(count(*)::text, 10)
                              || ' | '
                              || to_char(min(test_timestamp), 'YYYY-MM-DD HH24:MI:SS')
                              || ' | '
                              || to_char(max(test_timestamp), 'YYYY-MM-DD HH24:MI:SS')
                              || ' |' as result_data
                            from compat_tools.compat_testing
                           group by case when test_ok then 'PASSED' when not test_ok then 'FAILED' else 'NULL' end
        loop
            raise notice '%', l_app_name;
        end loop;

        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Test Detail (Failed or Null): ';
        raise notice '-- =====================================================================';
        select count(*) into l_failed_cnt
          from compat_tools.compat_testing
         where test_ok is null or (not test_ok);
        if l_failed_cnt = 0
        then
            raise notice '-- <<< ALL SUCCEED >>>';
        else
            for l_app_name in select '   | test_expression                              | result          | expect          | complete_time       |'
                               union all
                              select '   |----------------------------------------------|-----------------|-----------------|---------------------|'
                               union all
                              select '   | '
                                  || case when length(test_expr) > 44 then substr(test_expr, 1, 40)||' ...' else rpad(test_expr, 44) end
                                  || ' | '
                                  || lpad(coalesce(test_result, ' '), 15)
                                  || ' | '
                                  || rpad(coalesce(expect_result, ' '), 15)
                                  || ' | '
                                  || to_char(test_timestamp, 'YYYY-MM-DD HH24:MI:SS')
                                  || ' |' as result_data
                                from compat_tools.compat_testing
                               where test_ok is null or (not test_ok)
            loop
                raise notice '%', l_app_name;
            end loop;
        end if;
    end if;
end;
$RESULT_SUMMARY$ language plpgsql;

\q
